﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace SQL_VS
{
    public partial class Form1 : Form
    {
        MySqlConnection conn=null; //连接数据库对象
        MySqlDataAdapter adapter; //适配器变量
        DataSet set;  //临时数据集

        public Form1()
        {
            InitializeComponent();


        }

        private void button1_Click(object sender, EventArgs e)
        {
            
            string connstr = @"database = school; Password = 20001017; User ID = root; server = localhost; pooling = false; charset = utf8";
            conn = new MySqlConnection(connstr);
            conn.Open();
            MessageBox.Show("连接成功");

            string sql = "select* from welcome";
            adapter = new MySqlDataAdapter(sql, conn);
            //数据集、本地微型数据库可以存储多张表。
            set = new DataSet();
            //从数据库的stuinfo表中取出数据
            adapter.Fill(set, "welcome");
            //将取出的数据做为dataGridView1的数据源
            dataGridView1.DataSource = set;
            dataGridView1.DataMember = "welcome";
        }

        private void button8_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if(conn==null)
            {
                MessageBox.Show("请建立连接！");
                return;
            }

            if (comboBox1.Text == "")
            {
                MessageBox.Show("请选择一张表");
                return;
            }

            if(textBox1.Text == "" && textBox7.Text == "")
            {
                string sql = "select* from "+comboBox1.Text;
                adapter = new MySqlDataAdapter(sql, conn);
                //数据集、本地微型数据库可以存储多张表。
                set = new DataSet();
                //从数据库的stuinfo表中取出数据
                adapter.Fill(set, "welcome");
                //将取出的数据做为dataGridView1的数据源
                dataGridView1.DataSource = set;
                dataGridView1.DataMember = "welcome";
            }else if(textBox1.Text != "" && textBox7.Text != "")
            {
                MessageBox.Show("请选择按照主键值查询或者sql命令查询");
                return;
            }else if(textBox1.Text != "" && textBox7.Text == "")
            {
                string sql = "select * from " + comboBox1.Text + " where "+label1.Text+" = "+'"'+textBox1.Text+'"';
                Console.WriteLine(sql);
                adapter = new MySqlDataAdapter(sql, conn);
                //数据集、本地微型数据库可以存储多张表。
                set = new DataSet();
                //从数据库的stuinfo表中取出数据
                adapter.Fill(set, "welcome");
                //将取出的数据做为dataGridView1的数据源
                dataGridView1.DataSource = set;
                dataGridView1.DataMember = "welcome";

            }else if(textBox1.Text == "" && textBox7.Text != "")
            {
                string sql = textBox7.Text;
                Console.WriteLine(sql);
                adapter = new MySqlDataAdapter(sql, conn);
                //数据集、本地微型数据库可以存储多张表。
                set = new DataSet();
                //从数据库的stuinfo表中取出数据
                adapter.Fill(set, "welcome");
                //将取出的数据做为dataGridView1的数据源
                dataGridView1.DataSource = set;
                dataGridView1.DataMember = "welcome";
            }


        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if(comboBox1.Text=="图书")
            {
                label1.Text = "图书编号";
                label2.Text = "书名";
                label3.Text = "作者";
                label4.Text = "出版社编号";
                label5.Text = "出版社名";
                label6.Text = "图书类别";


            }
            else if(comboBox1.Text == "员工表")
            {
                label1.Text = "工号";
                label2.Text = "姓名";
                label3.Text = "性别";
                label4.Text = "部门编号";
                label5.Text = "部门名";
                label6.Text = "职务";
            }
            else if(comboBox1.Text == "销售表")
            {
                label1.Text = "售书单号";
                label2.Text = "序号";
                label3.Text = "图书编号";
                label4.Text = "数量";
                label5.Text = "销售日期";
                label6.Text = "销售人员工号";
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            int result = 0;

            if (conn == null)
            {
                MessageBox.Show("请建立连接！");
                return;
            }

            if (textBox1.Text != "" && textBox7.Text == "")
            {
                string sql = "insert into " + comboBox1.Text + "("+label1.Text+","+label2.Text+","+label3.Text+","+label4.Text+","+label5.Text+","+label6.Text+")"+" values("+'"'+textBox1.Text+'"'+","+'"' + textBox2.Text + '"' + "," + '"' + textBox3.Text + '"'+","+ '"' + textBox4.Text + '"' + "," + '"' + textBox5.Text + '"' + "," + '"' + textBox6.Text + '"' + ")";
                //数据集、本地微型数据库可以存储多张表。
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                result = cmd.ExecuteNonQuery();
            }
            else if (textBox1.Text == "" && textBox7.Text != "")
            {
                string sql = textBox7.Text;
                //数据集、本地微型数据库可以存储多张表。
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                result = cmd.ExecuteNonQuery();
            }
            else if (textBox1.Text == "" && textBox7.Text == "")
            {
                MessageBox.Show("请选择按照主键值删除或者sql命令删除！");
                return;
            }

            if (result == 0)
            {
                MessageBox.Show("操作失败");
            }
            else
            {
                MessageBox.Show("增加成功！");
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            int result=0;

            if (conn == null)
            {
                MessageBox.Show("请建立连接！");
                return;
            }


            if (textBox1.Text != "" && textBox7.Text == "")
            {
                string sql = "delete from " + comboBox1.Text + " where " + label1.Text + "=" + '"' + textBox1.Text + '"';
                //数据集、本地微型数据库可以存储多张表。
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                result = cmd.ExecuteNonQuery();
            }else if(textBox1.Text == "" && textBox7.Text != "")
            {
                string sql = textBox7.Text;
                //数据集、本地微型数据库可以存储多张表。
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                result = cmd.ExecuteNonQuery();
            }else if(textBox1.Text == "" && textBox7.Text == "")
            {
                MessageBox.Show("请选择按照主键值删除或者sql命令删除！");
                return;
            }
            

            if (result == 0)
            {
                MessageBox.Show("操作失败");
            }
            else
            {
                MessageBox.Show("删除成功！");
            }


        }

        private void button5_Click(object sender, EventArgs e)
        {
            if (conn == null)
            {
                MessageBox.Show("请建立连接！");
                return;
            }

            int result = 0;

            if (conn == null)
            {
                MessageBox.Show("请建立连接！");
                return;
            }
            //"update 员工表 set 姓名 = \"尔尔\" 性别 = \"女\" 部门编号 = \"04\" 部门名 = \"人事部\" 职务 = \"经理\"  where 工号 = \"1207\"";
            if (textBox1.Text != "" && textBox7.Text == "")
            {
                string sql = "update " + comboBox1.Text + " set " +
                    label2.Text + " = " + '"' + textBox2.Text + '"' + "," +
                    label3.Text + " = " + '"' + textBox3.Text + '"' + "," +
                    label4.Text + " = " + '"' + textBox4.Text + '"' + "," +
                    label5.Text + " = " + '"' + textBox5.Text + '"' + "," +
                    label6.Text + " = " + '"' + textBox6.Text + '"' + " " +
                    " where " + label1.Text + " = " + '"' + textBox1.Text + '"';
                //数据集、本地微型数据库可以存储多张表。
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                result = cmd.ExecuteNonQuery();
            }
            else if (textBox1.Text == "" && textBox7.Text != "")
            {
                string sql = textBox7.Text;
                //数据集、本地微型数据库可以存储多张表。
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                result = cmd.ExecuteNonQuery();
            }
            else if (textBox1.Text == "" && textBox7.Text == "")
            {
                MessageBox.Show("请选择按照主键值删除或者sql命令删除！");
                return;
            }
           

            if (result == 0)
            {
                MessageBox.Show("操作失败");
            }
            else
            {
                MessageBox.Show("更新成功！");
            }


        }

        private void button6_Click(object sender, EventArgs e)
        {
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
            textBox6.Text = "";
        }

        private void button7_Click(object sender, EventArgs e)
        {
            if (conn == null)
            {
                MessageBox.Show("请建立连接！");
                return;
            }
            textBox7.Text = "";
        }
    }
}
